package com.example.tagsdemo.service.impl;

import com.example.tagsdemo.builder.ConcreteBuilder;
import com.example.tagsdemo.conf.DBconf;
import com.example.tagsdemo.dto.ColumnDTO;
import com.example.tagsdemo.dto.GeneratorDTO;
import com.example.tagsdemo.dto.TableDTO;
import com.example.tagsdemo.entity.DBTable;
import com.example.tagsdemo.entity.DBTableColumns;
import com.example.tagsdemo.enums.ResultEnum;
import com.example.tagsdemo.handler.AbstractHandler;
import com.example.tagsdemo.handler.HandlerContext;
import com.example.tagsdemo.service.DBService;
import com.example.tagsdemo.utils.Form2DTO;
import com.example.tagsdemo.utils.ResultVOUtil;
import com.example.tagsdemo.vo.ResultVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@Service
@Slf4j
public class DBServiceImpl implements DBService {

    @Autowired
    private DBconf dBconf;

    @Autowired
    private HandlerContext handlerContext;
    /**
     * 获取数据库连接
     * @return
     */
    private Connection getConnection() {
        //加载驱动
        Connection connection = null;
        try {
            Class.forName(dBconf.getDriverClassName());
            Properties props =new Properties();
            props.setProperty("user", dBconf.getUsername());
            props.setProperty("password", dBconf.getPassword());
            props.setProperty("remarks", "true"); //设置可以获取remarks信息
            props.setProperty("useInformationSchema", "true");//设置可以获取tables remarks信息
//            connection = DriverManager.getConnection(dBconf.getUrl(), dBconf.getUsername(), dBconf.getPassword());
            connection = DriverManager.getConnection(dBconf.getUrl(),props);
        } catch (ClassNotFoundException e) {
            log.error("can not load jdbc driver", e);
        } catch (SQLException e) {
            log.error("get connection failure", e);
        }
        return connection;
    }

    /**
     * * 关闭数据库连接
     * * @param conn
     */
    private void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure", e);
            }
        }
    }

    /**
     * 获取所有表信息
     * @param
     * @return
     */
    @Override
    public List<DBTable> getTables() {
        Connection connection = this.getConnection();
        //获得元数据
        DatabaseMetaData metaData = null;
        ResultSet tables = null;
        List<DBTable> result = new ArrayList<>();
        try {
            metaData = connection.getMetaData();
            tables = metaData.getTables(connection.getCatalog(), null, null, new String[]{"TABLE"});
            while (tables.next()) {
                DBTable dbTable = new DBTable();
                String tableName = tables.getString("TABLE_NAME");
                dbTable.setName(tableName);
                //无法获取到表描述信息时
                //1 在数据库url连接后增加useInformationSchema=true
                //2 代码34 35行
                dbTable.setRemarks(tables.getString("REMARKS"));
                dbTable.setList(getColumns(connection,metaData,tableName));
                result.add(dbTable);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                tables.close();
                closeConnection(connection);
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return result;
    }

    /**
     * 获取某个表的信息
     * @param tableName
     * @return
     */
    @Override
    public DBTable getTables(String tableName) {
        List<DBTable> tableList = this.getTables();
        for(DBTable table : tableList) {
            if (table.getName().equalsIgnoreCase(tableName)) {
               return table;
            }
        }
       return null;
    }

    /**
     * 获取表的字段信息
     * @param
     * @return
     */
    @Override
    public  List<DBTableColumns> getColumns(String tableName) {
        Connection connection = this.getConnection();
        //获得元数据
        DatabaseMetaData metaData = null;
        ResultSet tables = null;
        List<DBTableColumns> result = new ArrayList<>();
        try {
            metaData = connection.getMetaData();
            //获得表信息
            tables = metaData.getColumns(connection.getCatalog(), "%", tableName, null);
            while (tables.next()) {
                DBTableColumns dbColumn = new DBTableColumns();
                dbColumn.setColumnName(tables.getString("COLUMN_NAME"));
                dbColumn.setColumnSize(tables.getInt("COLUMN_SIZE"));
                dbColumn.setTypeName(tables.getString("TYPE_NAME"));
                dbColumn.setRemarks(tables.getString("REMARKS"));
                result.add(dbColumn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                tables.close();
                closeConnection(connection);
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return result;
    }

    private List<DBTableColumns> getColumns(Connection connection,DatabaseMetaData metaData,String tableName) {
        //获得元数据
        ResultSet tables = null;
        List<DBTableColumns> result = new ArrayList<>();
        try {
            //获得表信息
            tables = metaData.getColumns(connection.getCatalog(), "%", tableName, null);
            while (tables.next()) {
                DBTableColumns dbColumn = new DBTableColumns();
                dbColumn.setColumnName(tables.getString("COLUMN_NAME"));
                dbColumn.setColumnSize(tables.getInt("COLUMN_SIZE"));
                dbColumn.setTypeName(tables.getString("TYPE_NAME"));
                dbColumn.setRemarks(tables.getString("REMARKS"));
                result.add(dbColumn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public ResultVO createTable(TableDTO tableDTO) {
        DBTable tables = this.getTables(tableDTO.getTableName());
        if (null != tables) {
            log.error("table {} is exists",tableDTO.getTableName());
            return ResultVOUtil.error(ResultEnum.TABLE_IS_EXISTS);
        }
        Connection connection = this.getConnection();
        if (null != connection) {
            List<ColumnDTO> columnDTOList = Form2DTO.tableDTO2DTO(tableDTO);
            ConcreteBuilder concreteBuilder = new ConcreteBuilder(tableDTO.getTableName(), tableDTO.getRemarks(), columnDTOList);
            String build = concreteBuilder.build();
            Statement st = null;
            try {
                st = connection.createStatement();
                st.executeUpdate(build);
            } catch (SQLException e) {
                log.error("create table fail,{}",e);
                return ResultVOUtil.error(ResultEnum.CREATE_TABLE_ERROR);
            }finally {
                this.closeConnection(connection);
            }
        }
        return ResultVOUtil.success();
    }


    @Override
    public ResultVO create(GeneratorDTO dto) {
        AbstractHandler handler = handlerContext.getInstance(dto.getType());
        return handler.handle(dto);
    }

}
